<?php
/**
 * PostgreSql访问类
 * @author Terry
 * @copyright baoling@yeah.net
 * 
 */
Class DB_Pgsql{
    /**
     * 数据库请求次数
     *
     * @var Int
     */
    var $query_num = 0;
    /**
     * 数据库连接信息
     *
     * @var Array
     */
    var $dbinfo=null;
    /**
     * 数据库连接句柄
     *
     * @var resource
     */
    var $conn = null;
    /**
     * 最后一次数据库操作的错误信息
     *
     * @var mixed
     */

    var $lasterr = null;
    /**
     * 最后一次数据库操作的错误代码
     *
     * @var mixed
     */
    var $lasterrcode=null;
    /**
     * 指示事务是否启用了事务
     *
     * @var int
     */
    var $_transflag = false;
    /**
     * 启用事务处理情况下的错误
     *
     * @var Array
     */
    var $_transErrors = array();
    /**
     * 最后一次查询的结果
     *
     * @var mixed
     */
    var $_lastrs = null;
            
    function DB_Pgsql($dbinfo){
        if(is_array($dbinfo)){
            $this->dbinfo=$dbinfo;
        }else{
            showError(__T('Miss argument!Please check file conf.php!'));
        }
    }

    /**
     * 数据库连接
     *
     * @param Array $dbinfo
     * @return boolean
     */
    function connect($dbinfo=false) {
        
        if ($this->conn && $dbinfo == false) { return true; }
        
        if (!$dbinfo) {
            $dbinfo = $this->dbinfo;
        } else {
            $this->dbinfo = $dbinfo;
        }
        
        if (isset($dbinfo['port']) && $dbinfo['port'] != '') {
            $host = $dbinfo['host'] . ':' . $dbinfo['port'];
        } else {
            $host = $dbinfo['host'];
        }
        
        if (!isset($dbinfo['dbpass'])){ $dbinfo['dbpass'] = ''; }

        $dsnstring = '';
        if (isset($dbinfo['host'])) {
            $dsnstring = 'host=' . $this->_addslashes($dbinfo['host']);
        }
        if (isset($dbinfo['port'])) {
            $dsnstring .= ' port=' . $this->_addslashes($dbinfo['port']);
        }
        if (isset($dbinfo['dbuser'])) {
            $dsnstring .= ' user=' . $this->_addslashes($dbinfo['dbuser']);
        }
        if (isset($dbinfo['dbpass'])) {
            $dsnstring .= ' password=' . $this->_addslashes($dbinfo['dbpass']);
        }
        if (isset($dbinfo['dbname'])) {
            $dsnstring .= ' dbname=' . $this->_addslashes($dbinfo['dbname']);
        }
        $dsnstring .= ' ';

        if (isset($dbinfo['options'])) {
            $this->conn = pg_connect($dsnstring, $dbinfo['options']);
        } else {
            $this->conn = pg_connect($dsnstring);
        }

        if (!$this->conn){
            showError(sprintf(__T('Connect to PostgreSQL (%s,%s) failed!'),$host,$dbinfo['dbuser']));
        }

        if (!$this->query("set datestyle='ISO'")) { return false; }

        if (isset($dbinfo['charset']) && $dbinfo['charset'] != '') {
            $charset = $dbinfo['charset'];
        } else {
            $charset = "GBK";
        }
        if (strtoupper($charset) == 'GB2312') { $charset = 'GBK'; }

        if ($charset != '') {
            pg_set_client_encoding($this->conn, $charset);
        }
    
        return true;
    }

    /**
     * 关闭数据库连接
     *
     */
    function close() {
        if ($this->conn) {
            pg_close($this->conn);
        }
        $this->conn = null;
    }
    
    function q_field($tableName){
        if (substr($tableName, 0, 1) == '"') { return $tableName; }
        return '"' . $tableName . '"';
    }
    
    function q_str($value){
        if(!$this->conn){
            $this->connect();
        }
        
        if (is_bool($value)) { return $value ? 1:0; }
        if (is_null($value)) { return 'NULL'; }
        
        return "'".pg_escape_string($value)."'";
    }
    /**
     * 直接查询Sql
     *
     * @param String $SQL
     * @return Mix
     */
    function query($sql) {
        if(!$this->conn){
            $this->connect();
        }
        //$query = @mysql_query($SQL,$this->conn);

        $query = @pg_exec($this->conn, $sql);
        $this->query_num++;
        if (!$query){
            $this->lasterr = pg_errormessage($this->conn);
            $this->lasterrcode = NULL;
            if($this->_transflag){
                $this->_transErrors[]['sql'] = $sql;
                $this->_transErrors[]['errcode'] = $this->lasterrcode;
                $this->_transErrors[]['err'] = $this->lasterr;
            }else{
                showError('SQL:' . $SQL .' ERROR_INFO:'.$this->lasterrcode.','.$this->lasterr);
            }
            return false;
        }else{
            $this->_lastrs = $query;
            $this->lasterr = null;
            $this->lasterrcode = null;
            return $query;
        }
    }
    
    function getAll($sql){
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->query($sql);
        }
        
        $data = array();
               while ($row = @pg_fetch_assoc($res)) {
            $data[] = $row;
            }
           @pg_free_result($res);
           
        return $data;
    }

    function getOne($sql)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->query($sql);
        }

        $row = @pg_fetch_row($res);
        @pg_free_result($res);
        return isset($row[0]) ? $row[0] : null;
    }

    /**
     * 执行查询，返回第一条记录
     *
     * @param string|resource $sql
     *
     * @return mixed
     */
    function getRow($sql)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->query($sql);
        }
        $row = @pg_fetch_assoc($res);
        @pg_free_result($res);
        return $row;
    }

    /**
     * 执行查询，返回结果集的指定列
     *
     * @param string|resource $sql
     * @param int $col 要返回的列，0 为第一列
     *
     * @return mixed
     */
    function getCol($sql, $col = 0)
    {
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->query($sql);
        }
        $data = array();
        while ($row = @pg_fetch_row($res)) {
            $data[] = $row[$col];
        }
        @pg_free_result($res);
        return $data;
    }
    
    function getAssoc($sql){
        if (is_resource($sql)) {
            $res = $sql;
        } else {
            $res = $this->query($sql);
        }
        $data = array();
        while ($row = @pg_fetch_row($res)) {
            $data[$row[0]] = $row[1];
        }
        @pg_free_result($res);
        return $data;
    }
    /**
     * 加入边界的查询语句
     *
     * @param String $sql
     * @param Int $length
     * @param Int $offset
     * @return Resource
     */
    function selectLimit($sql, $length = null, $offset = null)
    {
        if ($length != null) { $length = (int)$length; }
        $sql = sprintf('%s LIMIT %d OFFSET %d', $sql, $length, (int)$offset);

        return $sql;
    }
    /**
     * 返回数组
     *
     * @param resouce $query
     * @return Array
     */
    function fetchArray($query) {
        return @pg_fetch_array($query);
    }
    /**
     * 返回最近一次数据库操作受到影响的记录数
     *
     * @return int
     */
    function affectedRows() {
        if (is_resource($this->_lastrs)) { return @pg_affected_rows($this->_lastrs); }
        return 0;
    }
    /**
     * 从记录集中返回一行数据
     *
     * @param resouce $query
     *
     * @return array
     */
    function fetchRow($query) {
        return @pg_fetch_row($query);
    }

    /**
     * Enter description here...
     *
     * @param resouce $query
     * @return Int
     */
    function numRows($query) {
        if(is_resource($query))
        $rows = @pg_num_rows($query);
        else{
            $rows = @pg_num_rows($this->query($query));
        }
        return $rows;
    }
    /**
     * 获取当前PostgreSQL的版本号
     *
     * @return String
     */
    function version() {
        return pg_version($this->conn);
    }
    /**
     * 获得刚插入数据的ID号
     *
     * @return Int
     */
    function insertId() {
        $id = 0;
        return $id;
    }
     /**
     * 返回数据库可以接受的日期格式
     *
     * @param int $timestamp
     */
    function dbTimeStamp($timestamp)
    {
        return date('Y-m-d H:i:s', $timestamp);
    }
    /**
     * 获得查询数据库的次数
     *
     * @return Int
     */
    function getQueryNum(){
        return $this->query_num;
    }
    /**
     *  按照 PostgreSQL 的要求转义 dbinfo 字符串参数
     *
     * @param string $s
     *
     * @return string
     */
    function _addslashes($s)
    {
        $len = strlen($s);
        if ($len == 0) return "''";
        if (strncmp($s,"'",1) === 0 && substr($s,$len-1) == "'") return $s; // already quoted
        return "'".addslashes($s)."'";
    }
    function strRandom(){
        return 'RANDOM()';
    }
    /**
     * 启动事务
     */
    function startTrans()
    {
        $rs = $this->query('BEGIN');
        $this->_transflag = true;
        $this->_transErrors = array();
        return $rs;
    }

    /**
     * 提交事务
     *
     */
    function commit()
    {
        $this->_transflag = false;
        $rs = $this->query('COMMIT');
        return $rs;
    }
    /**
     * 回滚事务
     *
     */
    function rollback(){
        $this->_transflag = false;
        $rs = $this->query('ROLLBACK');
        return $rs;
    }
    
    function transErrors(){
        $errors = $this->_transErrors;
        if(is_array($errors)){
            foreach($errors as $error){
                echo 'SQL:' . $error['sql'] .' ERROR_INFO:'.$error['errcode'].','.$error['err'];
            }
        }
        die();
    }
}
